package com.sh.ssao.lottery.config.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
@Slf4j
public class ExcelMergeUtils {
    /**
     * 处理Excel数据
     * @param file
     * @return
     */
    public List<Map<String,Object>> impDataAnalysis(File file, String[] beanNames){
        List<Map<String,Object>> list = new ArrayList<>();
        // 工作表
        Workbook workbook = getWorkbook(file);//WorkbookFactory.create(xlsFile);
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        System.out.println("表个数:"+numberOfSheets);
        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 获取总行数，得到总行数。
            int rowNumbers = sheet.getLastRowNum() + 1;
            System.out.println("总行数"+rowNumbers);
            //获取送货时间
            Row row1 = sheet.getRow(1);
            Cell cell = row1.getCell(6);
            String date = String.valueOf(getValue(cell));
            //行循环
            for (int k = 3; k<rowNumbers; k++) {
                Map<String, Object> map = new HashMap<>();
                Row row = sheet.getRow(k);
                Cell cell1 = row.getCell(1);
                Cell cell2 = row.getCell(2);
                Cell cell3 = row.getCell(3);
                Cell cell4 = row.getCell(4);
                Cell cell5 = row.getCell(5);
                Cell cell6 = row.getCell(6);
                //名称为空结束循环
                if(null == cell1 || "".equals(String.valueOf(cell1))){
                    break;
                }
                //字段为空返回空对象
                if(null==cell2||null==cell3||null==cell4||null==cell5||null==cell6
                        ||"".equals(String.valueOf(cell2))||"".equals(String.valueOf(cell3))
                        ||"".equals(String.valueOf(cell4))||"".equals(String.valueOf(cell5))
                        ||"".equals(String.valueOf(cell6))){
                    return null;
                }
                if (k==40) {
                    System.out.println(k);
                }
                // 获取数据
                String value1 = String.valueOf(getValue(cell1));
                String value2 = String.valueOf(getValue(cell2));
                String value3 = String.valueOf(getValue(cell3));
                String value4 = String.valueOf(getValue(cell4));
                String value5 = String.valueOf(getValue(cell5));
                String value6 = String.valueOf(getValue(cell6));

                map.put("foodName", value1);
                map.put("foodUnit", value2);
                map.put("unitPrice", value3);
                map.put("foodQuantity", value4);
                map.put("totalPrice", value5);
                map.put("supplierName", value6);
                map.put("storageTime",date);

                list.add(map);
            }
        }
        log.debug("整合数据："+list.toString());
        return list;
    }

    /**
     * 获取workbook
     *
     * @return
     */
    private Workbook getWorkbook(File file){
        Workbook wb = null;
        try {
            FileInputStream fis = new FileInputStream(file);
            return new XSSFWorkbook(fis);
        }catch (IOException e){
            log.error("导入数据出错",e);
            return wb;
        }
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private static  boolean isMergedRegion(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }
    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @return
     */
    private static  int getMergedRowNum(Sheet sheet) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            return (lastRow-firstRow);
        }
        return 1;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return null==String.valueOf(getValue(fCell))||"".equals(String.valueOf(getValue(fCell)))?"0":String.valueOf(getValue(fCell)) ;
                }
            }
        }
        return null ;
    }

    /**
     * 获取单元格的值
     * int CELL_TYPE_NUMERIC = 0;
     * int CELL_TYPE_STRING = 1;
     * int CELL_TYPE_FORMULA = 2;
     * int CELL_TYPE_BLANK = 3;
     * int CELL_TYPE_BOOLEAN = 4;
     * int CELL_TYPE_ERROR = 5;
     * @param
     * @return
     */
    private static Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

    public static void main(String[] args) {
        String[] beanNames = {"counterpartyName","tradeAccountName","budgetYear","budgetAmount","orgId"};
        File file = new File("d://impBudget.xlsx");
        List<Map<String, Object>> maps = new ExcelMergeUtils().impDataAnalysis(file,beanNames);

        System.out.println("-------------end----------");
    }
}
